﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Security;
using System.Web.WebPages;
using System.Web.WebPages.Scope;
using WebMatrix.Data;
using WebMatrix.WebData;

public static class Freemium {
    private const string DefaultSignUpPageUrl = "~/Freemium/Signup";
    private const string DefaultCreateSkuPageUrl = "~/Freemium/CreateSKU/";
    private const string DefaultEditSkuPageUrl = "~/Freemium/EditSKU/";
    private const string DefaultEditFeaturePageUrl = "~/Freemium/EditFeature/";
    private const string DefaultSignUpForSkuPageUrl = "~/Freemium/SignupForSKU/";
    private const string DefaultSkuImagePageUrl = "~/Freemium/GetSkuImage";

    private const string StyleSheetUrl = "~/Freemium/Content/Default.css";    
   
    private static object _roleProviderKey;
    private static object _databaseNameKey;
    private static object _signUpPageUrlKey;

    static Freemium() {
        _roleProviderKey = new object();
        _databaseNameKey = new object();
        _signUpPageUrlKey = new object();        
    }

    public static RoleProvider RoleProvider {
        get {
            return (RoleProvider)(ScopeStorage.CurrentScope[_roleProviderKey] ?? Roles.Provider);
        }
        
        set {
            if (value == null) {
                throw new ArgumentNullException("RoleProvider");
            }

            ScopeStorage.CurrentScope[_roleProviderKey] = value;
        }
    }

    public static string DatabaseName {
        get {
            return (string)(ScopeStorage.CurrentScope[_databaseNameKey] ?? "");
        }
        
        set {
            if (value == null) {
                throw new ArgumentNullException("DatabaseName");
            }
            
            ScopeStorage.CurrentScope[_databaseNameKey] = value;
        }
    }

    public static string SignUpPageUrl {
        get {
            return (string)(ScopeStorage.CurrentScope[_signUpPageUrlKey] ?? "");
        }

        set {
            if (value == null) {
                throw new ArgumentNullException("SignUpPageUrl");
            }

            ScopeStorage.CurrentScope[_signUpPageUrlKey] = value;
        }
    }

    /// <summary>
    /// Initializes the Freemium helper. Using 'signUpPageUrl' you can specify the sign up page where to redirect users that cannot access a specific feature. 
    /// If 'createBaseDataSet' is set to true a base set of SKU and features will be created automatically in this database.
    /// </summary>
    /// <param name="databaseName">The name of the database containing the Freemium data</param>
    /// <param name="createBaseDataSet">If true, creates a base set of features and SKU in the database</param>
    /// <param name="signUpPageUrl">The relative url of the Sign Up page where to redirect users that cannot access a specific page</param>
    public static void Initialize(string databaseName, bool createBaseDataSet = false, string signUpPageUrl = DefaultSignUpPageUrl) {
        DatabaseName = databaseName;
        SignUpPageUrl = signUpPageUrl;

        InitializeDB(createBaseDataSet);
    }

    /// <summary>
    /// Returns if a feature is enabled for the current user
    /// </summary>
    /// <param name="featureName">The name of the feature</param>
    /// <returns>True if the feature is enabled, and otherwise false</returns>
    public static bool IsFeatureEnabledForCurrentUser(string featureName) {
        if (!WebSecurity.IsAuthenticated) {
            return false;
        }

        return IsFeatureEnabledForUser(featureName, WebSecurity.CurrentUserName);
    }

    /// <summary>
    /// Returns if a feature is enabled for a given user
    /// </summary>
    /// <param name="featureName">The name of the feature</param>
    /// <param name="userName">The user name</param>
    /// <returns>True if the feature is enabled, and otherwise false</returns>
    public static bool IsFeatureEnabledForUser(string featureName, string userName) {
        if (featureName.IsEmpty()) {
            throw new ArgumentNullException("featureName");
        }

        if (userName.IsEmpty()) {
            throw new ArgumentNullException("userName");
        }

        var db = Database.Open(DatabaseName);        
        var stockKeepingUnits = db.Query("SELECT s.* FROM freemium_StockKeepingUnit s, freemium_FeatureStockKeepingUnit fs, freemium_Feature f WHERE f.Name = @0 AND f.Id = fs.FeatureId AND s.Id = fs.StockKeepingUnitId", featureName);
        var roles = RoleProvider.GetRolesForUser(userName);

        return stockKeepingUnits.Any((dynamic sku) => roles.Any(role => string.Equals(role, sku.Name, StringComparison.OrdinalIgnoreCase)));
    }

    /// <summary>
    /// Returns if a feature is enabled for a given Stock Keeping Unit
    /// </summary>
    /// <param name="featureName">The name of the feature</param>
    /// <param name="skuName">The name of the sku</param>
    /// <returns>True if the feature is enabled, and otherwise false</returns>
    public static bool IsFeatureEnabledForStockKeepingUnit(string featureName, string skuName)
    {
        if (featureName.IsEmpty()) {
            throw new ArgumentNullException("featureName");
        }

        if (skuName.IsEmpty()) {
            throw new ArgumentNullException("skuName");
        }

        var db = Database.Open(DatabaseName);
        var query = db.QuerySingle("SELECT * FROM freemium_StockKeepingUnit s, freemium_FeatureStockKeepingUnit fs, freemium_Feature f WHERE f.Name = @0 AND s.Name = @1 AND f.Id = fs.FeatureId AND s.Id = fs.StockKeepingUnitId", featureName, skuName);

        return query != null;
    }

    /// <summary>
    /// Validates if the current user has acceess to the current page. If not, the user is redirected to the Sign Up page
    /// </summary>
    /// <param name="featureName">The name of the feature to validate</param>
    public static void RequireFeatureEnabledForCurrentPage(string featureName) {
        if (!IsFeatureEnabledForCurrentUser(featureName)) {
            HttpContext.Current.Response.Redirect(string.Format("{0}/{1}", SignUpPageUrl, featureName));
        }
    }

    /// <summary>
    /// Retrieves all the features
    /// </summary>
    /// <returns>The list of features of your web site</returns>
    public static IEnumerable<dynamic> GetAllFeatures() {
        var db = Database.Open(DatabaseName);
        var features = db.Query("SELECT f.* FROM freemium_Feature f");

        return features;
    }

    /// <summary>
    /// Retrieves all the SKUs
    /// </summary>
    /// <returns>The list of SKU of your web site</returns>
    public static IEnumerable<dynamic> GetAllStockKeepingUnits() {
        var db = Database.Open(DatabaseName);
        var stockKeepingUnits = db.Query("SELECT s.* FROM freemium_StockKeepingUnit s");

        return stockKeepingUnits;
    }

    /// <summary>
    /// Checks if an associated SKU exists for the given role
    /// </summary>
    /// <param name="roleName">The name of the role</param>
    /// <returns>True if an associated SKU exists for the role, false otherwise</returns>
    public static bool ExistsStockKeepingForRole(string roleName) {
        if (roleName.IsEmpty()) {
            throw new ArgumentNullException("rolename");
        }

        var db = Database.Open(DatabaseName);        
        var sku = db.QuerySingle("SELECT ID FROM freemium_StockKeepingUnit sku WHERE sku.Name = @0", roleName);

        return sku != null;
    }

    /// <summary>
    /// Shows a 'Create/Edit SKU' link for the associated role
    /// </summary>
    /// <param name="rolename">The name of the role</param>             
    /// <param name="createCssClass">The name of the CSS Class to display the 'Create SKU' link</param>
    /// <param name="createPageUrl">The URL to redirect when the user clicks the 'Create SKU' link</param>
    /// <param name="editCssClass">The name of the CSS Class to display the 'Edit SKU' link</param>
    /// <param name="editPageUrl">The URL to redirect when the user clicks the 'Edit SKU' link</param>
    /// <returns>The HTML code for the link to edit or create an associate SKU for the role</returns>
    public static HtmlString GetStockKeepingUnitLinkHtml(
        string rolename,
        string createCssClass = "createSku",
        string createPageUrl = DefaultCreateSkuPageUrl,
        string editCssClass = "editSku",
        string editPageUrl = DefaultEditSkuPageUrl)
    {
        var builder = new StringBuilder();

        RenderSyleSheetLink(builder);

        if (!Freemium.ExistsStockKeepingForRole(rolename)) {
            builder.Append(string.Format(@"<a href=""{0}"" class=""{1}"" />Create SKU</a>", Href(createPageUrl + rolename), createCssClass));
        }
        else {
            builder.Append(string.Format(@"<a href=""{0}"" class=""{1}"" />Edit SKU</a>", Href(editPageUrl + rolename), editCssClass));
        }

        return new HtmlString(builder.ToString());
    }

    /// <summary>
    /// Retrieves all the enabled features for a SKU
    /// </summary>
    /// <param name="skuName">The name of the SKU</param>
    /// <returns>The list of enabled features</returns>
    public static IEnumerable<dynamic> GetAllFeaturesForStockKeepingUnit(string skuName) {
        if (skuName.IsEmpty()) {
            throw new ArgumentNullException("skuName");
        }

        var db = Database.Open(DatabaseName);
        var features = db.Query("SELECT f.*, fsku.* FROM freemium_Feature f, freemium_FeatureStockKeepingUnit fsku, freemium_StockKeepingUnit s WHERE f.id = fsku.FeatureId AND s.id = fsku.StockKeepingUnitId AND s.Name = @0", skuName);

        return features;
    }

    /// <summary>
    /// Retrieves all the SKUs where a feature is enabled
    /// </summary>
    /// <param name="featureName">The name of the feature</param>
    /// <returns>The list of SKUs where the feature is enabled</returns>
    public static IEnumerable<dynamic> GetAllStockKeepingUnitsForFeature(string featureName) {
        if (featureName.IsEmpty()) {
            throw new ArgumentNullException("featureName");
        }

        var db = Database.Open(DatabaseName);        
        var stockKeepingUnits = db.Query("SELECT fsku.*, s.* FROM freemium_Feature f, freemium_FeatureStockKeepingUnit fsku, freemium_StockKeepingUnit s WHERE f.id = fsku.FeatureId AND s.id = fsku.StockKeepingUnitId AND f.Name = @0", featureName);

        return stockKeepingUnits;
    }

    /// <summary>
    /// Retrieves a feature 
    /// </summary>
    /// <param name="featureName">The name of the feature</param>
    /// <returns>The feature</returns>
    public static dynamic GetFeature(string featureName) {
        if (featureName.IsEmpty()) {
            throw new ArgumentNullException("featureName");
        }

        var db = Database.Open(DatabaseName);
        
        return db.QuerySingle("SELECT * FROM freemium_Feature f WHERE f.Name = @0", featureName);
    }

    /// <summary>
    /// Creates a new Feature
    /// </summary>
    /// <param name="name">The name of the feature</param>
    /// <param name="description">The description of the feature</param>
    public static void CreateFeature(string name, string description) {
        if (name.IsEmpty()) {
            throw new ArgumentNullException("name");
        }

        var db = Database.Open(DatabaseName);
        db.Execute("INSERT INTO Freemium_Feature (Name, Description) VALUES (@0, @1)", name, description);
    }

    /// <summary>
    /// Updates a feature
    /// </summary>
    /// <param name="name">The name of the feature</param>
    /// <param name="description">The description of the feature</param>    
    public static void UpdateFeature(string name, string description) {
        if (name.IsEmpty()) {
            throw new ArgumentNullException("name");
        }

        var db = Database.Open(DatabaseName);
        db.Execute("UPDATE Freemium_Feature SET Description=@0 WHERE Name=@1", description, name);
    }

    /// <summary>
    /// Removes a feature
    /// </summary>
    /// <param name="featureName">The name of the feature to be removed</param>
    public static void RemoveFeature(string featureName) {
        if (featureName.IsEmpty()) {
            throw new ArgumentNullException("featureName");
        }

        var db = Database.Open(DatabaseName);
        db.Execute("DELETE FROM Freemium_FeatureStockKeepingUnit WHERE FeatureId IN (SELECT Id from freemium_Feature WHERE Name = @0)", featureName);
        db.Execute("DELETE FROM Freemium_Feature WHERE Name = @0", featureName);
    }

    /// <summary>
    /// Shows the SKU/Features matrix, to display which features are enabled in each SKU
    /// </summary>
    /// <param name="tableCssClass">The CSS Class for the table</param>
    /// <param name="enabledCssClass">The CSS Class to use in the cell corresponding to an enabled feature</param>
    /// <param name="disabledCssClass">The CSS Class to use in the cell corresponding to a disabled feature</param>
    /// <param name="editFeaturePageUrl">The feature editing page URL</param>
    /// <param name="editSkuPageUrl">The SKU editing page URL</param>
    /// <param name="allowEdit">Weather the matrix supports editing</param>
    /// <param name="includeFormTag">Weather to include a surrounding form tag to the HTML table</param>
    /// <param name="saveText">The text displayed in the save button</param>
    /// <returns>The HTML code for the SKU/Features matrix</returns>
    public static IHtmlString GetFeatureByStockKeepingUnitMatrixHtml(
            string tableCssClass = "featureSkuMatrix",
            string enabledCssClass = "featureEnabled",
            string disabledCssClass = "featureDisabled",
            string editFeaturePageUrl = DefaultEditFeaturePageUrl,
            string editSkuPageUrl = DefaultEditSkuPageUrl,
            bool allowEdit = false,
            bool includeFormTag = false,
            string saveText = "Update")
    {
        var skus = GetAllStockKeepingUnits();
        var features = GetAllFeatures();
        var builder = new StringBuilder();

        RenderSyleSheetLink(builder);

        if (allowEdit && includeFormTag) {
            builder.Append(string.Format(@"<form method=""post"" action = """">"));
        }

        builder.Append(string.Format(@"<table class=""{0}"">", tableCssClass));
        builder.Append(@"<thead>");
        builder.Append(@"<tr>");
        builder.Append(@"<th>");
        builder.Append(@"</th>");

        foreach (var sku in skus) {
            builder.Append(@"<th>");
            if (allowEdit) {
                builder.Append(string.Format(@"<a href=""{0}"" />{1}</a>", Href(editSkuPageUrl + sku.Name), HttpUtility.HtmlEncode(sku.Name)));
            }
            else {
                builder.Append(HttpUtility.HtmlEncode(sku.Name));
            }

            builder.Append(@"</th>");
        }

        builder.Append(@"</tr>");
        builder.Append(@"</thead>");
        builder.Append(@"<tbody>");

        foreach (dynamic feature in features) {
            builder.Append(@"<tr>");
            builder.Append(@"<th>");
            if (allowEdit) {
                builder.Append(string.Format(@"<a href=""{0}"" />{1}</a>", Href(editFeaturePageUrl + feature.Name), HttpUtility.HtmlEncode(feature.Name)));
            }
            else {
                builder.Append(HttpUtility.HtmlEncode(feature.Name));
            }

            builder.Append(@"</th>");

            foreach (var sku in skus) {
                var isEnabled = IsFeatureEnabledForStockKeepingUnit(feature.Name, sku.Name);
                var cellClass = isEnabled ? enabledCssClass : disabledCssClass;

                builder.Append(@"<td>");

                if (allowEdit) {
                    string checkboxName = string.Format(CultureInfo.InvariantCulture, "sku_feature_{0}_{1}", sku.Id, feature.Id);
                    builder.Append(string.Format(@"<input id=""{0}"" name=""{0}"" type=""checkbox"" {1} />", checkboxName, isEnabled ? @"checked=""checked""" : ""));
                } else {
                    builder.Append(string.Format(@"<span class=""{0}""></span>", cellClass));
                }

                builder.Append(@"</td>");
            }

            builder.Append(@"</tr>");
        }

        builder.Append(@"</tbody>");
        builder.Append(@"</table>");

        if (allowEdit) {
            if (includeFormTag) {
                builder.Append(string.Format(@"<input type=""submit"" value = ""{0}"" />", saveText));
                builder.Append(@"</form>");
            }

            RenderCheckboxScripts(builder, enabledCssClass, disabledCssClass);
        }

        return new HtmlString(builder.ToString());
    }

    /// <summary>
    /// Shows the SKUs offered by your web site as an HTML list (ul). This method supports passing an inline template parameter to customize the look and feel of each SKU in the list.
    /// </summary>
    /// <param name="itemContainerCssClass">A CSS class to apply to each item in the list (li)</param>
    /// <param name="itemCssClass">A CSS class to apply to each item in the list</param>
    /// <param name="template">An inline template for each item in the list</param>
    /// <returns>The HTML code for the SKU</returns>
    public static IHtmlString GetStockKeepingUnitsListHtml(
            string itemContainerCssClass = "skuItemContainer",
            string itemCssClass = "skuItem",
            string signUpForSkuPageUrl = DefaultSignUpForSkuPageUrl,
            Func<dynamic, object> template = null)
    {
        var skus = GetAllStockKeepingUnits();
        var builder = new StringBuilder();

        RenderSyleSheetLink(builder);

        builder.Append(string.Format(@"<ul class=""{0}"">", itemContainerCssClass));

        foreach (var sku in skus) {
            builder.Append(string.Format(@"<li class=""{0}"">", itemCssClass));

            if (template != null) {
                builder.Append(template(sku)); 
            } else {
                builder.Append(GetStockKeepingUnitImageHtml(sku.Name).ToString());
                builder.Append(string.Format(@"<h3>{0}</h3>", HttpUtility.HtmlEncode(sku.Name)));
                builder.Append(string.Format(@"<h4>${0}</h4>", HttpUtility.HtmlEncode(sku.Price)));
                builder.Append(string.Format(@"<h5>{0}</h5>", HttpUtility.HtmlEncode(sku.Description)));

                if (sku.Price > 0) {
                    builder.Append(string.Format(@"<a href=""{0}"">Sign Up</a>", Href(DefaultSignUpForSkuPageUrl + sku.Name)));
                }
            }

            builder.Append(@"</li>");
        }

        builder.Append(@"</ul>");

        return new HtmlString(builder.ToString());
    }

    /// <summary>
    /// Retrieves the image of the specified Stock Keeping Unit.
    /// </summary>
    /// <param name="skuName">The name of the Stock Keeping Unit</param>
    /// <returns>An array of bytes representing the image</returns>
    public static byte[] GetStockKeepingUnitImage(string skuName) {
        var db = Database.Open(Freemium.DatabaseName);
        var image = db.QueryValue("SELECT skui.Image Image from Freemium_StockKeepingUnit sku, Freemium_StockKeepingUnitImage skui WHERE sku.Name = @0 AND skui.StockKeepingUnitId = sku.Id", skuName);
        
        if (image != null) {
                return image;
        } else {
            return CreateStockKeepingUnitImagePlaceholder();
        }            
    }

    /// <summary>
    /// Shows an HTML image tag for the Stock Keeping Unit image
    /// </summary>
    /// <param name="skuName">The Stock Keeping Unit name</param>
    /// <param name="imageCssClass">The name of the CSS class to apply to the image tag</param>
    /// <param name="skuImagePageUrl">The url of the page that serves the Stock Keeping Unit images</param>
    /// <returns>The HTML code for the Stock Keeping Unit image</returns>
    public static IHtmlString GetStockKeepingUnitImageHtml(
            string skuName,
            string imageCssClass = "skuImage",
            string skuImagePageUrl = DefaultSkuImagePageUrl)
    {
        if (skuName.IsEmpty()) {
            throw new ArgumentNullException("skuName");
        }
        
        var builder = new StringBuilder();

        builder.Append(string.Format(@"<img src=""{0}"" alt=""{1}""></img>", Href(skuImagePageUrl + "/" + skuName), skuName));
        
        return new HtmlString(builder.ToString());
    }
    
    /// <summary>
    /// Removes a SKU
    /// </summary>
    /// <param name="skuName">The SKU name</param>
    public static void RemoveStockKeepingUnit(string skuName) {
        if (skuName.IsEmpty()) {
            throw new ArgumentNullException("skuName");
        }

        var db = Database.Open(DatabaseName);
        db.Execute("DELETE FROM Freemium_FeatureStockKeepingUnit WHERE StockKeepingUnitId IN (SELECT Id from Freemium_StockKeepingUnit sku WHERE sku.Name = @0)", skuName);
        db.Execute("DELETE Freemium_StockKeepingUnitImage WHERE StockKeepingUnitId IN (SELECT Id from Freemium_StockKeepingUnit sku WHERE sku.Name = @0)", skuName);
        db.Execute("DELETE FROM Freemium_StockKeepingUnit WHERE Name = @0", skuName);
    }

    /// <summary>
    /// Creates a new SKU
    /// </summary>
    /// <param name="name">The name of the SKU</param>
    /// <param name="description">The description</param>
    /// <param name="price">The price of the SKU</param>
    /// <param name="image">Image to show behind SKU Description</param>
    public static void CreateStockKeepingUnit(string name, string description, decimal price, byte[] image = null) {
        if (name.IsEmpty()) {
            throw new ArgumentNullException("name");
        }

        var db = Database.Open(DatabaseName);
        db.Execute("INSERT INTO Freemium_StockKeepingUnit (Name, Description, Price) VALUES (@0, @1, @2)", name, description, price);
        
        if (image != null) {
            var skuId = db.GetLastInsertId();
            db.Execute("INSERT INTO Freemium_StockKeepingUnitImage (StockKeepingUnitId, Image) VALUES (@0, @1)", skuId, image);
        }
    }

    /// <summary>
    /// Updates a SKU
    /// </summary>
    /// <param name="name">The name of the SKU</param>
    /// <param name="description">The description of the SKU</param>
    /// <param name="price">The price of the SKU</param>
    /// <param name="image">Image to show behind SKU Description</param>
    public static void UpdateStockKeepingUnit(string name, string description, decimal price, bool updateImage = false, byte[] image = null) {
        if (name.IsEmpty()) {
            throw new ArgumentNullException("name");
        }

        var db = Database.Open(DatabaseName);
        db.Execute("UPDATE Freemium_StockKeepingUnit SET Description = @0, Price = @1 WHERE Name = @2", description, price, name);

        if (updateImage) {
            var sku = db.QuerySingle("SELECT Id FROM Freemium_StockKeepingUnit sku where sku.Name = @0", name);            
            
            if (sku != null) {
                var id = sku.Id;
                
                if (image != null) {
                    var skuImage = db.QuerySingle("SELECT Image FROM Freemium_StockKeepingUnitImage sku where StockKeepingUnitId=@0", id);

                    if (skuImage != null) {
                        db.Execute("UPDATE Freemium_StockKeepingUnitImage SET Image=@0 WHERE StockKeepingUnitId=@1", image, id);
                    } else {
                        db.Execute("INSERT INTO Freemium_StockKeepingUnitImage (StockKeepingUnitId, Image) VALUES (@0, @1)", id, image);
                    }
                } else {
                    db.Execute("DELETE Freemium_StockKeepingUnitImage WHERE StockKeepingUnitId=@0", id);
                }
            }
        }
    }

    /// <summary>
    /// Retrieves a SKU
    /// </summary>
    /// <param name="skuName">The name of the SKU</param>
    /// <returns>The SKU</returns>
    public static dynamic GetStockKeepingUnit(string skuName) {
        if (skuName.IsEmpty()) {
            throw new ArgumentNullException("skuName");
        }

        var db = Database.Open(DatabaseName);
        return db.QuerySingle("SELECT * FROM freemium_StockKeepingUnit sku WHERE sku.Name = @0", skuName);
    }

    /// <summary>
    /// Enables or disables a feature for a SKU
    /// </summary>
    /// <param name="featureName">The name of the Feature</param>
    /// <param name="skuName">The name of the SKU</param>
    /// <param name="enable">If true, enables the feature for the sku, if false the feature is disabled</param>    
    public static void UpdateFeatureForStockKeepingUnit(string featureName, string skuName, bool enable) {
        var db = Database.Open(DatabaseName);
        
        if (!enable) {
            db.Execute("DELETE FROM freemium_FeatureStockKeepingUnit WHERE FeatureId IN (SELECT Id FROM freemium_Feature f WHERE f.Name = @0) AND StockKeepingUnitId IN (SELECT Id FROM freemium_StockKeepingUnit s WHERE s.Name = @1)", featureName, skuName);
        } else {
            var result = db.QuerySingle("SELECT * FROM freemium_FeatureStockKeepingUnit fs, freemium_Feature f, freemium_StockKeepingUnit s WHERE fs.FeatureId = f.Id AND fs.StockKeepingUnitId = s.Id AND f.Name = @0 AND s.Name = @1", featureName, skuName);
            
            if (result == null) {
                db.Execute("INSERT INTO freemium_FeatureStockKeepingUnit (FeatureId, StockKeepingUnitId) SELECT f.Id, s.Id FROM freemium_Feature f, freemium_StockKeepingUnit s WHERE f.Name = @0 AND s.Name = @1", featureName, skuName);
            }             
        }
    }

    /// <summary>
    /// Updates the SKU/Features matrix using the data in the submited Form
    /// </summary>    
    public static void UpdateFeatureByStockKeepingUnitMatrix() {
        var db = Database.Open(DatabaseName);

        /// Clear old matrix configuration
        db.Execute("DELETE FROM freemium_FeatureStockKeepingUnit");

        foreach (string key in HttpContext.Current.Request.Form.AllKeys) {
            var skuIdStartIndex = "sku_feature_".Length;

            if (key.StartsWith("sku_feature_")) {
                var featureId = key.Substring(key.LastIndexOf('_') + 1);
                var skuId = key.Substring(skuIdStartIndex, key.Length - skuIdStartIndex - featureId.Length - 1);

                /// Insert feature-sku association
                db.Execute("INSERT INTO freemium_FeatureStockKeepingUnit (FeatureId, StockKeepingUnitId) VALUES (@0, @1)", featureId, skuId);
            }
        }
    }

    private static string Href(string path) {
        if (HttpContext.Current != null) {
            return WebPageContext.Current.Page.Href(path);
        }

        return path;
    }

    private static void RenderSyleSheetLink(StringBuilder builder) {
        builder.AppendLine(string.Format(@"<link rel=""stylesheet"" href=""{0}"" type=""text/css""/>", Href(StyleSheetUrl)));
    }

    private static void RenderCheckboxScripts(StringBuilder builder, string enabledCssClass, string disabledCssClass) {
        builder.Append(@"<script src=""http://ajax.microsoft.com/ajax/jquery/jquery-1.4.2.js"" type=""text/javascript""></script>");        
        builder.Append(@"<script type=""text/javascript"">");
        builder.Append(@"$(document).ready(function() {");
        builder.Append(@"$("".fakecheck"").click(function() {");
        builder.Append(string.Format(@"($(this).hasClass('{0}')) ? $(this).removeClass('{0}').addClass('{1}') : $(this).removeClass('{1}').addClass('{0}');", enabledCssClass, disabledCssClass));
        builder.Append(@"return false;");
        builder.Append(@"});");
        builder.Append(@"});");
        builder.Append(@"$(document).ready(function() {");
        builder.Append(@"$("".fakecheck"").click(function() {");
        builder.Append(string.Format(@"($(this).hasClass('{0}')) ? $(this).removeClass('{0}').addClass('{1}') : $(this).removeClass('{1}').addClass('{0}');", enabledCssClass, disabledCssClass));
        builder.Append(@"$(this.hash).trigger(""click"");");
        builder.Append(@"return false;");
        builder.Append(@"});");
        builder.Append(@"});");
        builder.Append(@"$(document).ready(function() {");
        builder.Append(@"$(""input:checkbox"").each(function() {");
        builder.Append(@"var img = $('<a id=""fake' + this.id + '"" class=""fakecheck"" href=""#' + this.id + '"" />');");
        builder.Append(@"$(this).after(img);");
        builder.Append(@"$(this).css('display', 'none');");
        builder.Append(string.Format(@"(this.checked) ? $(""#fake"" + this.id).addClass('{0}') : $(""#fake"" + this.id).addClass('{1}');", enabledCssClass, disabledCssClass));
        builder.Append(@"});");
        builder.Append(@"$("".fakecheck"").click(function() {");
        builder.Append(string.Format(@"($(this).hasClass('{0}')) ? $(this).removeClass('{0}').addClass('{1}') : $(this).removeClass('{1}').addClass('{0}');", enabledCssClass, disabledCssClass));
        builder.Append(@"$(this.hash).trigger(""click"");");
        builder.Append(@"return false;");
        builder.Append(@"});");
        builder.Append(@"});");
        builder.Append(@"</script>");
    }

    private static void InitializeDB(bool createBaseDataSet) {
        var db = Database.Open(DatabaseName);
        db.Connection.Open();

        var tables = db.Connection.GetSchema("Tables");

        if (tables.Select("table_name = 'freemium_Feature'").Count() == 0) {
            db.Execute("CREATE TABLE [freemium_Feature] ([Id] int identity(1,1) NOT NULL, [Name] nvarchar(128) NOT NULL, [Description] nvarchar(1000) NULL)");
            db.Execute("ALTER TABLE [freemium_Feature] ADD CONSTRAINT [PK_Feature] PRIMARY KEY ([Id])");
            db.Execute("CREATE UNIQUE INDEX [IX_Feature_UniqueName] ON [freemium_Feature] ([Name] ASC)");
        }

        if (tables.Select("table_name = 'freemium_StockKeepingUnit'").Count() == 0) {
            db.Execute("CREATE TABLE [freemium_StockKeepingUnit] ([Id] int identity(1,1) NOT NULL, [Name] nvarchar(128) NOT NULL, [Description] nvarchar(1000) NULL, [Price] money NULL)");
            db.Execute("ALTER TABLE [freemium_StockKeepingUnit] ADD CONSTRAINT [PK_StockKeepingUnit] PRIMARY KEY ([Id])");
            db.Execute("CREATE UNIQUE INDEX [IX_StockKeepingUnit_UniqueName] ON [freemium_StockKeepingUnit] ([Name] ASC)");
        }

        if (tables.Select("table_name = 'freemium_FeatureStockKeepingUnit'").Count() == 0) {
            db.Execute("CREATE TABLE [freemium_FeatureStockKeepingUnit] ([FeatureId] int NOT NULL, [StockKeepingUnitId] int NOT NULL)");
            db.Execute("ALTER TABLE [freemium_FeatureStockKeepingUnit] ADD CONSTRAINT [PK_FeatureStockKeepingUnit] PRIMARY KEY ([FeatureId],[StockKeepingUnitId])");
            db.Execute("ALTER TABLE [freemium_FeatureStockKeepingUnit] ADD CONSTRAINT [FK_FeatureStockKeepingUnit_Feature] FOREIGN KEY ([FeatureId]) REFERENCES [freemium_Feature]([Id])");
            db.Execute("ALTER TABLE [freemium_FeatureStockKeepingUnit] ADD CONSTRAINT [FK_FeatureStockKeepingUnit_StockKeepingUnit] FOREIGN KEY ([StockKeepingUnitId]) REFERENCES [freemium_StockKeepingUnit]([Id])");
        }

        if (tables.Select("table_name = 'freemium_StockKeepingUnitImage'").Count() == 0) {
            db.Execute("CREATE TABLE [freemium_StockKeepingUnitImage] ([StockKeepingUnitId] int NOT NULL, [Image] image NULL)");
            db.Execute("ALTER TABLE [freemium_StockKeepingUnitImage] ADD CONSTRAINT [PK_StockKeepingUnitImage] PRIMARY KEY ([StockKeepingUnitId])");
            db.Execute("ALTER TABLE [freemium_StockKeepingUnitImage] ADD CONSTRAINT [FK_StockKeepingUnitImage_StockKeepingUnit] FOREIGN KEY ([StockKeepingUnitId]) REFERENCES [freemium_StockKeepingUnit]([Id])");
        }

        if (createBaseDataSet) {
            var features = db.Query("SELECT * FROM freemium_Feature");
            var skus = db.Query("SELECT * FROM freemium_StockKeepingUnit");

            if (skus.Count() == 0 && features.Count() == 0) {
                db.Execute("INSERT INTO [freemium_Feature] ([Name],[Description]) VALUES ('Feature 1','Description for Feature 1')");
                db.Execute("INSERT INTO [freemium_Feature] ([Name],[Description]) VALUES ('Feature 2','Description for Feature 2')");
                db.Execute("INSERT INTO [freemium_Feature] ([Name],[Description]) VALUES ('Feature 3','Description for Feature 3')");
                db.Execute("INSERT INTO [freemium_StockKeepingUnit] ([Name],[Description],[Price]) VALUES ('Gold','Gold Users',120)");
                db.Execute("INSERT INTO [freemium_StockKeepingUnit] ([Name],[Description],[Price]) VALUES ('Silver','Silver Users',70)");
                db.Execute("INSERT INTO [freemium_StockKeepingUnit] ([Name],[Description],[Price]) VALUES ('Free','Free Users',0)");
                db.Execute("INSERT INTO [freemium_FeatureStockKeepingUnit] ([FeatureId],[StockKeepingUnitId]) SELECT f.[Id], s.[Id] FROM freemium_Feature f, freemium_StockKeepingUnit s WHERE f.[Name] = 'Feature 1' AND s.[Name] = 'Gold'");
                db.Execute("INSERT INTO [freemium_FeatureStockKeepingUnit] ([FeatureId],[StockKeepingUnitId]) SELECT f.[Id], s.[Id] FROM freemium_Feature f, freemium_StockKeepingUnit s WHERE f.[Name] = 'Feature 2' AND s.[Name] = 'Gold'");
                db.Execute("INSERT INTO [freemium_FeatureStockKeepingUnit] ([FeatureId],[StockKeepingUnitId]) SELECT f.[Id], s.[Id] FROM freemium_Feature f, freemium_StockKeepingUnit s WHERE f.[Name] = 'Feature 3' AND s.[Name] = 'Gold'");
                db.Execute("INSERT INTO [freemium_FeatureStockKeepingUnit] ([FeatureId],[StockKeepingUnitId]) SELECT f.[Id], s.[Id] FROM freemium_Feature f, freemium_StockKeepingUnit s WHERE f.[Name] = 'Feature 1' AND s.[Name] = 'Silver'");
                db.Execute("INSERT INTO [freemium_FeatureStockKeepingUnit] ([FeatureId],[StockKeepingUnitId]) SELECT f.[Id], s.[Id] FROM freemium_Feature f, freemium_StockKeepingUnit s WHERE f.[Name] = 'Feature 2' AND s.[Name] = 'Silver'");
                db.Execute("INSERT INTO [freemium_FeatureStockKeepingUnit] ([FeatureId],[StockKeepingUnitId]) SELECT f.[Id], s.[Id] FROM freemium_Feature f, freemium_StockKeepingUnit s WHERE f.[Name] = 'Feature 1' AND s.[Name] = 'Free'");
            }
        }
    }

    private static byte[] CreateStockKeepingUnitImagePlaceholder() {
        var bitmap = new System.Drawing.Bitmap(1, 1);
        bitmap.SetPixel(0, 0, System.Drawing.Color.FromArgb(0, 255, 255, 255));

        var stream = new System.IO.MemoryStream();
        bitmap.Save(stream, System.Drawing.Imaging.ImageFormat.Png);

        return stream.ToArray();
    }
}